package ee.battleship.game;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;

import ee.battleship.classes.Game;
import ee.battleship.classes.Player;

public class DBConnection {
	private final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private final String DB_URL = "jdbc:mysql://mysql-cheesecake666.jelastic.dogado.eu/battleship";
//	private final String DB_URL = "jdbc:mysql://localhost:3306/battleship";
	private final String DB_USER = "root";
	private final String DB_PASSWORD = "zby4DJvIV9";
//	private final String DB_PASSWORD = "hello";
	
	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	public Connection createConnection() throws Exception {
		
		try {
			Class.forName(DB_DRIVER).newInstance();
			connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
		} catch (SQLException sqlException) {
			System.out.println("MySQL error:");
			System.out.print("Message: " + sqlException.getMessage());
		} catch (Exception e) {
			System.out.println("Exception:");
			System.out.println("Message: " + e.getMessage());
			e.printStackTrace();
		} 
		
		return connection;
	}
	
	public Player getPlayerByName(String username) throws SQLException {
		String query = "SELECT * FROM battleship.player WHERE name = (?)";
		Player player = null;
		
		try {	
			if (connection == null) {
				createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, username);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				player = new Player(Integer.parseInt(resultSet.getString("id")));
				player.setName(username);
				player.setLosses(resultSet.getInt("losses"));
				player.setWins(resultSet.getInt("wins"));
				player.setPlayedGames(resultSet.getInt("played_games"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return player;
	}
	
	public Player getPlayerById(int id) throws SQLException {
		String query = "SELECT * FROM battleship.player WHERE id = ?";
		Player player = null;
		
		try {
			
			if (connection == null) {
				createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, id);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				player = new Player(Integer.parseInt(resultSet.getString("id")));
				player.setName(resultSet.getString("name"));
				player.setLosses(resultSet.getInt("losses"));
				player.setWins(resultSet.getInt("wins"));
				player.setPlayedGames(resultSet.getInt("played_games"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return player;
	}
	
	public Player insertPlayer(String username) throws SQLException {
		String query = "INSERT INTO battleship.player (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?";
		Player player = null;
		
		int id = 0;
		try {
			if(connection == null) {
				createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, username);
			preparedStatement.setString(2, username);
			preparedStatement.executeUpdate();
			
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT LAST_INSERT_ID() FROM battleship.player");
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		player = new Player(id);
		player.setName(username);
		return player;
	}
	
	public Game getGameById(int id) throws SQLException {
		Game game = null;
		String query = "SELECT * FROM battleship.game AS game WHERE game.id = ?";

		try {
			if (connection == null) {
				createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, id);
			ResultSet resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()) {
				game = new Game(resultSet.getInt("id"));
				game.setStatus(resultSet.getInt("status"));
				Player player = getPlayerById(resultSet.getInt("winner_id"));
				game.setWinner(player);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return game;
	}
	
	public void insertPlayerToGame (Game gameInstance, Player gamePlayer) throws SQLException {
		String query = "INSERT INTO battleship.player_has_game (player_id, game_id) VALUES (?, ?)";
		
		Player player = getPlayerById(gamePlayer.getId());
		Game game = getGameById(gameInstance.getId());
		
		try {
			if (connection == null) {
				createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, player.getId());
			preparedStatement.setInt(2, game.getId());
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<Player> getLeaderBoard() {
		ArrayList<Player> players = new ArrayList<Player>();
		String query = "SELECT *, (player.wins - player.losses) / player.wins AS ratio FROM battleship.player WHERE player.wins > 0 ORDER BY ratio DESC";
		
		try {
			if (connection == null) {
				createConnection();
			}
			
			Statement statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			while (resultSet.next()) {
				Player player = getPlayerById(resultSet.getInt("id"));
				players.add(player);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return players;
	}
	public ArrayList<Game> getAllPlayedGames() {
		ArrayList<Game> games = new ArrayList<Game>();
		String query = "SELECT * FROM battleship.game WHERE status = 1";
		
		try {
			if (connection == null) {
				connection = createConnection();
			}
			
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery(query);
			
			while (resultSet.next()) {
				Game game = getGameById(resultSet.getInt("id"));
				ArrayList<Player> players = getGamePlayers(game);
				game.setPlayers(players);
				games.add(game);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return games;
	}
	
	public ArrayList<Player> getGamePlayers(Game game) {
		ArrayList<Player> players = new ArrayList<Player>();
		String query = "SELECT * FROM battleship.player_has_game WHERE game_id = ?;";
		
		try {
			if (connection == null) {
				connection = createConnection();
			}

			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, game.getId());
			ResultSet resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				Player player = getPlayerById(resultSet.getInt("player_id"));
				players.add(player);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return players;
	}
	
	public void updatePlayer(Player player) {		
		String query = "UPDATE battleship.player SET played_games = ? , wins = ? , losses = ? WHERE id = ?";
		
		try {
			if (connection == null) {
				connection = createConnection();
			}
			
			PreparedStatement preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, player.getPlayedGames());
			preparedStatement.setInt(2, player.getWins());
			preparedStatement.setInt(3, player.getLosses());
			preparedStatement.setInt(4, player.getId());
			int numRows = preparedStatement.executeUpdate();
			System.out.println("Player rows updated " + numRows);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void updateGame(Game game) {
		String query = "UPDATE battleship.game SET status = ? WHERE id = ?";
		
		try {
			if (connection == null) {
				connection = createConnection();
			}
			
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, game.getStatus());
			preparedStatement.setInt(2, game.getId());
			int numRows = preparedStatement.executeUpdate();
			System.out.println("Game rows updated "+ numRows);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Game insertGame() {
		Game game = null;
		
		String query = "INSERT INTO battleship.game (status) VALUES (?)";
		int id = 0; 
		
		try {
			if (connection == null) {
				createConnection();
			}
			
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setInt(1, 0);
			preparedStatement.executeUpdate();
			
			statement = connection.createStatement();
			ResultSet resultSet = statement.executeQuery("SELECT LAST_INSERT_ID() FROM battleship.player");
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		game = new Game(id);
		game.setStatus(0);
		return game;
	}
	
	public void closeConnection() throws Exception {
		try {
			if(resultSet != null) {
				resultSet.close();
			}
			
			if(statement != null) {
				statement.close();
			}
			
			if(preparedStatement != null) {
				preparedStatement.close();
			}
			
			if(connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			throw e;
		}
	}
}
